﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DataAccessLibrary;
using BusinessLayer.DTO;

namespace BusinessLayer
{
    public class ClsEmployee
    {
        public int EmployeeID { get; set; }
        public string LoginID { get; set; }
        public string PWD { get; set; }
        public string FullName { get; set; }
        public string FirstName { get; set; }
        public string MiddleName { get; set; }
        public string LastName { get; set; }
        public string Address { get; set; }
        public string City { get; set; }
        public string State { get; set; }
        public string Country { get; set; }
        public string Pin { get; set; }
        public string Phone { get; set; }
        public string PhoneHome { get; set; }
        public string EmailID { get; set; }
        public string Qualification { get; set; }
        public DateTime? DOB { get; set; }
        public string IdentityProof { get; set; }
        public int EmploymentID { get; set; }
        public string EmploymentName { get; set; }
        public int DesignationID { get; set; }
        public string Designation { get; set; }
        public int RoleID { get; set; }
        public string Role { get; set; }
        public int EmploymentStatusID { get; set; }
        public string EmploymentStatus { get; set; }
        public int DepartmentID { get; set; }
        public string Department { get; set; }
        public DateTime? JoiningDate { get; set; }
        public DateTime? LastLoginDate { get; set; }
        public DateTime? ResignDate { get; set; }
        public Utility.Gender Gender { get; set; }
        public Utility.MaritalStatus MaritalStatus { get; set; }
        public bool Salaried { get; set; }
        public decimal HourlyRate { get; set; }
        public decimal OverTimeRate { get; set; }
        public bool IsAccountLocked { get; set; }
        public bool LeaveCarryForward { get; set; }
        public int NoOfLeave { get; set; }
        public int BalanceLeave { get; set; }

        public List<SalaryStructureInfo> SalaryStructures { get; set; }

        public DataTable GetAllActiveEmployees()
        {
            using (OleDataClient data = new OleDataClient())
            {
                return data.GetDataTable("Select * from Employee where EmploymentStatusID not in (select EmploymentStatusID from EmploymentStatus where EmploymentStatus='" + Utility.EmpStatus.RESIGNED.ToString() + "')", "Employee");
            }
        }
        public DataTable GetAllEmployees()
        {
            using (OleDataClient data = new OleDataClient())
            {
                return data.GetDataTable("Select * from Employee ", "Employee");
            }
        }
        public void GetEmployee(int employeeID)
        {
            using (OleDataClient data = new OleDataClient())
            {
                DataTable employyeData = data.GetDataTable("Select * from Employee where EmployeeID=" + employeeID.ToString(), "Employee");
                if (employyeData.Rows.Count > 0)
                {
                    EmployeeID = Utility.GetNumber(employyeData.Rows[0]["EmployeeID"].ToString());
                    LoginID = employyeData.Rows[0]["LoginID"].ToString();
                    PWD = employyeData.Rows[0]["PWD"].ToString();
                    
                    FirstName = employyeData.Rows[0]["FirstName"].ToString();
                    MiddleName = employyeData.Rows[0]["MiddleName"].ToString();
                    LastName = employyeData.Rows[0]["LastName"].ToString();

                    FullName = FirstName;
                    if (MiddleName.Trim() != string.Empty)
                        FullName = FullName + " " + MiddleName;

                    if (LastName.Trim() != string.Empty)
                        FullName = FullName + " " + LastName;

                    Address = employyeData.Rows[0]["Address"].ToString();
                    City = employyeData.Rows[0]["City"].ToString();
                    State = employyeData.Rows[0]["State"].ToString();
                    Country = employyeData.Rows[0]["Country"].ToString();
                    Pin = employyeData.Rows[0]["Pin"].ToString();
                    Phone = employyeData.Rows[0]["Phone"].ToString();
                    PhoneHome = employyeData.Rows[0]["PhoneHome"].ToString();
                    EmailID = employyeData.Rows[0]["EmailID"].ToString();
                    Qualification = employyeData.Rows[0]["Qualification"].ToString();
                    DOB = Utility.GetDate(employyeData.Rows[0]["DOB"].ToString());
                    IdentityProof = employyeData.Rows[0]["IdentityProof"].ToString();
                    EmploymentID = Utility.GetNumber(employyeData.Rows[0]["EmploymentID"].ToString());
                    ClsEmploymentType objClsEmploymentType = new ClsEmploymentType();
                    objClsEmploymentType.GetEmployment(Utility.GetNumber(employyeData.Rows[0]["EmploymentID"].ToString()));
                    EmploymentName = objClsEmploymentType.EmploymentType;
                    DesignationID = Utility.GetNumber(employyeData.Rows[0]["DesignationID"].ToString());
                    ClsDesignation objClsDesignation = new ClsDesignation();
                    objClsDesignation.GetDesignation(Utility.GetNumber(employyeData.Rows[0]["DesignationID"].ToString()));
                    Designation = objClsDesignation.Designation;
                    RoleID = Utility.GetNumber(employyeData.Rows[0]["RoleID"].ToString());
                    ClsRoleMaster objClsRoleMaster = new ClsRoleMaster();
                    objClsRoleMaster.GetRole(Utility.GetNumber(employyeData.Rows[0]["RoleID"].ToString()));
                    Role = objClsRoleMaster.Role;
                    EmploymentStatusID = Utility.GetNumber(employyeData.Rows[0]["EmploymentStatusID"].ToString());
                    ClsEmploymentStatusType objClsEmploymentStatusType = new ClsEmploymentStatusType();
                    objClsEmploymentStatusType.GetEmploymentStatusTypes(Utility.GetNumber(employyeData.Rows[0]["EmploymentStatusID"].ToString()));
                    EmploymentStatus = objClsEmploymentStatusType.EmploymentStatus;
                    DepartmentID = Utility.GetNumber(employyeData.Rows[0]["DepartmentID"].ToString());
                    ClsDepartment objClsDepartment = new ClsDepartment();
                    objClsDepartment.GetDepartment(Utility.GetNumber(employyeData.Rows[0]["DepartmentID"].ToString()));
                    Department = objClsDepartment.Department;
                    JoiningDate = Utility.GetDate(employyeData.Rows[0]["DOB"].ToString());
                    LastLoginDate = Utility.GetDate(employyeData.Rows[0]["LastLoginDate"].ToString());
                    MaritalStatus = (Utility.MaritalStatus)Enum.Parse(typeof(Utility.MaritalStatus), employyeData.Rows[0]["MaritalStatus"].ToString());
                    Gender = (Utility.Gender)Enum.Parse(typeof(Utility.Gender), employyeData.Rows[0]["Gender"].ToString());
                    ResignDate = Utility.GetDate(employyeData.Rows[0]["ResignDate"].ToString());
                    Salaried = Convert.ToBoolean(employyeData.Rows[0]["Salaried"]);
                    HourlyRate = Convert.ToDecimal(employyeData.Rows[0]["HourlyRate"]);
                    OverTimeRate = Convert.ToDecimal(employyeData.Rows[0]["OverTimeRate"]);
                    IsAccountLocked = Convert.ToBoolean(employyeData.Rows[0]["AccountLocked"]);
                    LeaveCarryForward = Convert.ToBoolean(employyeData.Rows[0]["LeaveCarryForward"]);
                    int noOfLeave = 0;
                    int.TryParse(data.GetValue("select TotalLeave from AnnualLeave where EmployeeID=" + employeeID.ToString()), out noOfLeave);
                    NoOfLeave = noOfLeave;
                    int balanceLeave = 0;
                    int.TryParse(data.GetValue("select Balance  from AnnualLeave where EmployeeID=" + employeeID.ToString()), out balanceLeave);
                    BalanceLeave = balanceLeave;
                }
                if (employeeID == 0)
                {
                    ClsSalaryHead objClsSalaryHead = new ClsSalaryHead();
                    DataTable salaryData = objClsSalaryHead.GetAllSalaryHead();
                    SalaryStructures = new List<SalaryStructureInfo>();
                    foreach (DataRow dr in salaryData.Rows)
                    {
                        SalaryStructureInfo drr = new SalaryStructureInfo();
                        drr.SalaryHeadID = Utility.GetNumber(dr["SalaryHeadID"].ToString());
                        drr.SalaryHead = dr["SalaryHead"].ToString();
                        drr.Amount = 0;
                        drr.DeductSalaryIfAbsent = Convert.ToBoolean(dr["DeductSalaryIfAbsent"]);
                        drr.Debit = Convert.ToBoolean(dr["Debit"]);
                        SalaryStructures.Add(drr);
                    }
                }
                else
                {


                    SalaryStructures = new List<SalaryStructureInfo>();
                    SalaryStructures = GetSalaryStructure(employeeID);

                    //string sql = "SELECT SalaryHeads.SalaryHeadID,SalaryHeads.SalaryHead , SalaryStructure.Amount, SalaryHeads.DeductSalaryIfAbsent"
                    //+ " FROM SalaryHeads INNER JOIN SalaryStructure "
                    //+ " ON SalaryHeads.SalaryHeadID = SalaryStructure.SalaryHeadID "
                    //+ " where SalaryStructure.EmployeeID=" + employeeID.ToString();
                    //DataTable salaryData = data.GetDataTable(sql, "Salary");
                    //SalaryStructures = new SalaryStructure.DTSalaryStructureDataTable();
                    //foreach (DataRow dr in salaryData.Rows)
                    //{
                    //    SalaryStructure.DTSalaryStructureRow drSalaryStructureRow = SalaryStructures.NewDTSalaryStructureRow();
                    //    drSalaryStructureRow.SalaryHeadID = Utility.GetNumber(dr["SalaryHeadID"].ToString());
                    //    drSalaryStructureRow.SalaryHead = dr["SalaryHead"].ToString();
                    //    drSalaryStructureRow.Amount = Convert.ToDecimal(dr["Amount"]);
                    //    drSalaryStructureRow.DeductSalaryIfAbsent = Convert.ToBoolean(dr["DeductSalaryIfAbsent"]);
                    //    SalaryStructures.Rows.Add(drSalaryStructureRow);
                    //}
                }
            }
        }
        public bool SaveEmployee(string KeyForSave)
        {
            using (OleDataClient data = new OleDataClient())
            {
                if (KeyForSave == "add")
                {
                    string userID = FirstName.Substring(0, 3) + LastName.Substring(0, 2);
                    string password = "welcome";
                    for (int i = 1; i < 100000; i++)
                    {
                        string count = data.GetValue("select  count(*) from Employee where LoginID='" + userID + "'");

                        if (Utility.GetNumber(count) > 0)
                        {
                            userID = userID + i.ToString();
                        }
                        else
                        {
                            break;
                        }
                    }
                    data.StartTran();
                    try
                    {
                        data.ExecuteNonQuery("Insert into Employee (LoginID,PWD,FirstName,MiddleName,LastName,Address,City,State,"
                        + "Country,Pin,Phone,PhoneHome,EmailID,IdentityProof,Qualification,DOB,EmploymentID,DesignationID,RoleID,"
                        + "EmploymentStatusID,DepartmentID,JoiningDate,Gender,MaritalStatus,Salaried,HourlyRate,OverTimeRate,AccountLocked,LeaveCarryForward)"
                        + " values('" + userID + "','" + password + "','" + FirstName + "','" + MiddleName + "','" + LastName + "','"
                        + Address + "','" + City + "','" + State + "','" + Country + "','" + Pin + "','"
                        + Phone + "','" + PhoneHome + "','" + EmailID + "','" + IdentityProof + "','" + Qualification + "','"
                        + DOB.ToString() + "'," + EmploymentID.ToString() + "," + DesignationID.ToString() + ","
                        + RoleID.ToString() + "," + EmploymentStatusID.ToString() + "," + DepartmentID + ",'" + JoiningDate + "','" + Gender.ToString() + "','" + MaritalStatus.ToString() + "'," + Salaried.ToString() + "," + HourlyRate.ToString() + "," + OverTimeRate + "," + IsAccountLocked + "," + LeaveCarryForward + ")");
                        string autoID = data.GetValue("SELECT @@IDENTITY");

                        foreach (SalaryStructureInfo item in SalaryStructures)
                        {
                            string query = "insert into SalaryStructure (EmployeeID,SalaryHeadID,Amount) "
                                + " values (" + autoID + "," + item.SalaryHeadID.ToString() + "," + item.Amount.ToString() + " )";
                            data.ExecuteNonQuery(query);
                        }

                        //Add Leave Details in Annual Leave Table.
                        data.ExecuteNonQuery("Insert into AnnualLeave (EmployeeID,TotalLeave,Balance) values(" + Convert.ToInt32(autoID) + "," + NoOfLeave + "," + NoOfLeave + ")");

                        data.CommitTran();
                    }
                    catch (Exception ex)
                    {
                        data.RollBackTran();
                        throw ex;
                    }
                }
                else
                {
                    data.StartTran();
                    try
                    {
                        data.ExecuteNonQuery("Update Employee set "
                            + " FirstName = '" + FirstName + "', "
                            + " LastName = '" + LastName + "', "
                            + " Address = '" + Address + "', "
                            + " City = '" + City + "', "
                            + " State = '" + State + "', "
                            + " Country = '" + Country + "', "
                            + " Pin = '" + Pin + "', "
                            + " Phone = '" + Phone + "', "
                            + " PhoneHome = '" + PhoneHome + "', "
                            + " EmailID = '" + EmailID + "', "
                            + " IdentityProof = '" + IdentityProof + "', "
                            + " Qualification = '" + Qualification + "', "
                            + " DOB = '" + DOB + "', "
                            + " EmploymentID = " + EmploymentID + ", "
                            + " DesignationID = " + DesignationID + ", "
                            + " RoleID = " + RoleID + ", "
                            + " EmploymentStatusID = " + EmploymentStatusID + ", "
                            + " DepartmentID = " + DepartmentID + ", "
                            + " Gender = '" + Gender + "', "
                            + " MaritalStatus = '" + MaritalStatus + "', "
                            + " Salaried = " + Salaried + ", "
                            + " HourlyRate = " + HourlyRate + ", "
                            + " OverTimeRate = " + OverTimeRate + ", "
                            + " AccountLocked = " + IsAccountLocked + ", "
                            + " LeaveCarryForward = " + LeaveCarryForward
                            + " where EmployeeID=" + EmployeeID.ToString());

                        if (Salaried)
                        {
                            data.ExecuteNonQuery("Delete from SalaryStructure where EmployeeID=" + EmployeeID.ToString());

                            foreach (SalaryStructureInfo item in SalaryStructures)
                            {
                                string query = "insert into SalaryStructure (EmployeeID,SalaryHeadID,Amount) "
                                    + " values (" + EmployeeID.ToString() + "," + item.SalaryHeadID.ToString() + "," + item.Amount.ToString() + " )";
                                data.ExecuteNonQuery(query);
                            }
                        }
                        //Update AnnualLeave Table for selected Employee
                        data.ExecuteNonQuery("Update AnnualLeave set TotalLeave=" + NoOfLeave + " where EmployeeID=" + EmployeeID.ToString());
                       
                        //Update no of time Failded Login attempto 0 in case Admin is unlocking account.
                        string strNoofFailedAttemp = data.GetValue("select FailedLoginAttempt from Employee where EmployeeID=" + EmployeeID.ToString() + "");
                        int intNoofFailedAttemp = 0;
                        int.TryParse(strNoofFailedAttemp, out intNoofFailedAttemp);
                        if (Convert.ToInt32(intNoofFailedAttemp) == 3 && IsAccountLocked == false)
                        {
                            data.ExecuteNonQuery("Update Employee set FailedLoginAttempt= 0 where EmployeeID=" + EmployeeID.ToString());
                        }
                        else if (IsAccountLocked)
                        {
                            data.ExecuteNonQuery("Update Employee set FailedLoginAttempt= 3 where EmployeeID=" + EmployeeID.ToString());
                        }

                        data.CommitTran();
                    }
                    catch (Exception ex)
                    {
                        data.RollBackTran();
                        throw ex;
                    }
                }
            }
            return true;
        }
        public void DeleteEmployee()
        {
            using (OleDataClient data = new OleDataClient())
            {
                string str = "select EmploymentStatusID from EmploymentStatus where EmploymentStatus='" + Utility.EmpStatus.RESIGNED.ToString() + "' ";
                string StatusID = data.GetValue(str);
                int statusID = 0;
                int.TryParse(StatusID, out statusID);
                data.ExecuteNonQuery("Update Employee  set EmploymentStatusID = " + StatusID.ToString()
                + " where EmployeeID=" + EmployeeID.ToString());
            }
        }
        public List<SalaryStructureInfo> GetSalaryStructure(int employeeID)
        {
            List<SalaryStructureInfo> SalaryStructures = new List<SalaryStructureInfo>();
            using (OleDataClient data = new OleDataClient())
            {
                string sql = "SELECT SalaryHeads.SalaryHeadID,SalaryHeads.SalaryHead , SalaryStructure.Amount,SalaryHeads.Debit, SalaryHeads.DeductSalaryIfAbsent"
                        + " FROM SalaryHeads INNER JOIN SalaryStructure "
                        + " ON SalaryHeads.SalaryHeadID = SalaryStructure.SalaryHeadID "
                        + " where SalaryStructure.EmployeeID=" + employeeID.ToString();
                DataTable salaryData = data.GetDataTable(sql, "Salary");
                foreach (DataRow dr in salaryData.Rows)
                {
                    SalaryStructureInfo drSalaryStructureRow = new SalaryStructureInfo();
                    drSalaryStructureRow.SalaryHeadID = Utility.GetNumber(dr["SalaryHeadID"].ToString());
                    drSalaryStructureRow.SalaryHead = dr["SalaryHead"].ToString();
                    drSalaryStructureRow.Amount = Convert.ToDecimal(dr["Amount"]);
                    drSalaryStructureRow.DeductSalaryIfAbsent = Convert.ToBoolean(dr["DeductSalaryIfAbsent"]);
                    drSalaryStructureRow.Debit = Convert.ToBoolean(dr["Debit"]);
                    SalaryStructures.Add(drSalaryStructureRow);
                }

                 sql = "SELECT SalaryHeadID,SalaryHead , 0 as Amount,Debit, DeductSalaryIfAbsent"
                        + " FROM SalaryHeads "
                        + " where SalaryHeadID NOT IN (select SalaryHeadID from SalaryStructure where  EmployeeID=" + employeeID.ToString() + ")";

                salaryData = data.GetDataTable(sql, "Salary");
                foreach (DataRow dr in salaryData.Rows)
                {
                    SalaryStructureInfo drSalaryStructureRow = new SalaryStructureInfo();
                    drSalaryStructureRow.SalaryHeadID = Utility.GetNumber(dr["SalaryHeadID"].ToString());
                    drSalaryStructureRow.SalaryHead = dr["SalaryHead"].ToString();
                    drSalaryStructureRow.Amount = Convert.ToDecimal(dr["Amount"]);
                    drSalaryStructureRow.DeductSalaryIfAbsent = Convert.ToBoolean(dr["DeductSalaryIfAbsent"]);
                    drSalaryStructureRow.Debit = Convert.ToBoolean(dr["Debit"]);
                    SalaryStructures.Add(drSalaryStructureRow);
                }
                return SalaryStructures;
            }
        }
    }
}
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                